package com.appsl.persistance;

import java.util.ArrayList;
import java.util.List;

import com.appsl.objects.Lugar;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DDL 
{
	public static void createSchema(SQLiteDatabase db)
	{
		String qr;
		qr = "CREATE TABLE Lugar (" +
				"idlugar integer not null primary key, " +
				"nombre text not null, " +
				"coordX double not null, " +
				"coordY double not null, " +
				"direccion text not null);";
		db.execSQL(qr);
		
		qr = "CREATE TABLE Favorito (" +
				"idlocation int, " +
				"idusuario int, " +
				"fecharegistro long, " +
				"estado int);";
		db.execSQL(qr);

		qr = "CREATE TABLE ParkingLot (" +
				"idparkinglot int not null, " +
				"idlugar int not null, " +
				"nombre text);";
		db.execSQL(qr);

		qr = "CREATE TABLE Piso (" +
				"idpiso int not null, " +
				"idparkinglot int not null, " +
				"numero text);";
		db.execSQL(qr);

		qr = "CREATE TABLE ParkingSpot (" +
				"idparkingspot int not null, " +
				"idlevel int not null, " +
				"estado int);";
		db.execSQL(qr);

		qr = "CREATE TABLE Parameters (" +
				"tabla text not null, " +
				"valorIntCampo int not null, " +
				"valorStrCampo text);";
		db.execSQL(qr);
		
		qr = "CREATE TABLE Usuario (" +
				"idusuario integer not null primary key, " +
				"nombre text not null, " +
				"password text not null, " +
				"loginvia text not null, " +
				"estado int not null, " +
				"fechacreacion long not null);";
		db.execSQL(qr);			
	}
	
	public static void dropSchema(SQLiteDatabase db)
	{
		db.execSQL("DROP TABLE Lugar");
		db.execSQL("DROP TABLE Favorito");
		db.execSQL("DROP TABLE ParkingLot");
		db.execSQL("DROP TABLE Piso");
		db.execSQL("DROP TABLE ParkingSpot");
		db.execSQL("DROP TABLE Parameters");
		db.execSQL("DROP TABLE Usuario");
	}

	public static void mergeLugar(SQLiteDatabase db, Lugar loc) 
	{
		//only for registering when synchronising with database. User won't add new places.
		String query = String.format("SELECT count(1) FROM Lugar WHERE idlugar = %d", loc.getIdlugar());
		Cursor crs = db.rawQuery(query, null);
		crs.moveToFirst();
		
		if (crs.getInt(0) == 0)
		{
			query = "INSERT INTO Lugar VALUES (" +
						loc.getIdlugar() + ", " +
						"\"" + loc.getName() + "\", " +
						loc.getX() + ", " +
						loc.getY() + ", " +
						"\"" + loc.getDireccion() + "\");";
			db.execSQL(query);
		}
		else
		{
			query = "UPDATE Lugar SET " +
						"nombre = \"" + loc.getName() + "\", " +
						"coordX = " + loc.getX() + ", " +
						"coordY = " + loc.getY() + ", " +
						"direccion = \"" + loc.getDireccion() + "\" " +
					"WHERE idlugar = " + loc.getIdlugar() + ";";
			db.execSQL(query);
		}
		
		crs.close();
	}

	public static Lugar findLocationByName(Activity act, String nm) 
	{
		SQLiteDatabase db = OpenHelper.getInstance(act);
		String query = String.format("SELECT * FROM Lugar WHERE nombre = \"%s\"", nm);
		Cursor crs = db.rawQuery(query, null);
		crs.moveToFirst();
		
		if (crs.getCount() > 0)
		{
			Lugar loc = new Lugar();
			loc.setIdlugar(crs.getInt(0));
			loc.setName(crs.getString(1));
			loc.setX(crs.getDouble(2));
			loc.setY(crs.getDouble(3));
			
			return loc;
		}
		else
			return null;
	}
	
	public static List<Lugar> getLugares(Activity act){
		List<Lugar> lstLugares;
		SQLiteDatabase db = OpenHelper.getInstance(act);
		Cursor crs = db.rawQuery("SELECT * FROM Lugar", null);
		crs.moveToFirst();
		int lgs = crs.getCount();
		
		if (lgs == 0)
			lstLugares = null;
		else{
			lstLugares = new ArrayList<Lugar>();
			
			for (int i=0; i<lgs; i++){
				Lugar loc = new Lugar();
				loc.setIdlugar(crs.getInt(0));
				loc.setName(crs.getString(1));
				loc.setX(crs.getDouble(2));
				loc.setY(crs.getDouble(3));
				loc.setDireccion(crs.getString(4));
				lstLugares.add(loc);
			}
		}		
		
		return lstLugares;
	}
}
